﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CodeCamp12.Domain;
using System.Data.SqlClient;
using System.Configuration;
using log4net;

namespace CodeCamp12.Domain
{
   public class SessionDataManagament
    {
       private ConnectionStringProvider connString = new ConnectionStringProvider();
       private ILog log = LogManager.GetLogger("CodeCamp12.DAL");
      
       /// <summary>
       /// Gets all sessions for the event passed in parametar
       /// </summary>
       /// <param name="e"></param>
       /// <returns></returns>
       public List<Session> GetSessionsForEvent(Event e)
       {
           List<Session> sessionsList = null;
           var conn = connString.GetConnection();
           var comm = new SqlCommand("getSessionForEvent") {CommandType = System.Data.CommandType.StoredProcedure};
           comm.Parameters.AddWithValue("@EventID", e.EventID);
           comm.Connection = conn;
           try
           {
               conn.Open();
               var reader = comm.ExecuteReader();
               while (reader.Read())
               {
                   if(sessionsList == null)
                   {
                       sessionsList = new List<Session>();
                   }
                   var session = new Session
                                     {
                                         ID = new Guid(reader["ID"].ToString()),
                                         Title = reader["Title"].ToString(),
                                         Description = reader["Description"].ToString(),
                                         EventID = new Guid(reader["EventID"].ToString()),
                                         Language = reader["Language"].ToString(),
                                         Speakers = GetAllSpeakersForSession(reader["ID"].ToString())
                                     };
                   sessionsList.Add(session);
               }
               reader.Close();
           }
           catch (Exception ex)
           {
               log.Error(ex);
               throw;
           }
           finally
           {
               conn.Close();
           }
           return sessionsList;
       }

       /// <summary>
       /// Get all unused sessions (sessions that are still not put in some track)
       /// </summary>
       /// <param name="trackId"></param>
       /// <param name="eventId"></param>
       /// <returns></returns>
       public List<Session> GetSessionsNotInTrack(Guid trackId, Guid eventId)
       {
           var conn = connString.GetConnection();
           var comm = new SqlCommand("GetSessionsNotInTrack") { CommandType = System.Data.CommandType.StoredProcedure };
           comm.Parameters.AddWithValue("@TrackID", trackId);
           comm.Parameters.AddWithValue("@EventID", eventId);
           comm.Connection = conn;
           List<Session> sessionsList = null;
           try
           {
               conn.Open();
               var reader = comm.ExecuteReader();
               while (reader.Read())
               {
                   if(sessionsList == null)
                   {
                       sessionsList = new List<Session>();
                   }
                   var session = new Session
                   {
                       ID = new Guid(reader["ID"].ToString()),
                       Title = reader["Title"].ToString(),
                       Description = reader["Description"].ToString(),
                       EventID = new Guid(reader["EventID"].ToString()),
                       Language = reader["Language"].ToString(),
                       Speakers = GetAllSpeakersForSession(reader["ID"].ToString())
                   };
                   sessionsList.Add(session);
               }
               reader.Close();

           }
           catch (Exception ex)
           {
                
               log.Error(ex);
               throw;
           }
           finally
           {
               conn.Close();
           }
           return sessionsList;
       }

       /// <summary>
       /// Get sessions for the current event
       /// </summary>
       /// <returns></returns>
       public List<Session> GetSessionsForCurrent()
       {
           
           List<Session> sessionsList = null;
           var conn = connString.GetConnection();
           var comm = new SqlCommand("GetSessionsForCurrentEvent")
                          {CommandType = System.Data.CommandType.StoredProcedure, Connection = conn};
           try
           {
               conn.Open();
               var reader = comm.ExecuteReader();
               while (reader.Read())
               {
                   if(sessionsList == null)
                   {
                       sessionsList = new List<Session>();
                   }
                   var session = new Session
                   {
                       ID = new Guid(reader["ID"].ToString()),
                       Title = reader["Title"].ToString(),
                       Description = reader["Description"].ToString(),
                       EventID = new Guid(reader["EventID"].ToString()),
                       Language = reader["Language"].ToString(),
                       Speakers = GetAllSpeakersForSession(reader["ID"].ToString())
                   };
                   sessionsList.Add(session);
               }
               reader.Close();

           }
           catch (Exception ex)
           {
                
               log.Error(ex);
               throw;
           }
           finally
           {
               conn.Close();
           }
           return sessionsList;
       }

       /// <summary>
       /// Get all used sessions (sessions that are put in some track)
       /// </summary>
       /// <param name="trackId"></param>
       /// <param name="eventId"></param>
       /// <returns></returns>
       public List<Session> GetSessionsInTrack(Guid trackId, Guid eventId)
       {

           List<Session> sessionsList = null;
           var conn = connString.GetConnection();
           var comm = new SqlCommand("GetSessionsInTrack", conn) { CommandType = System.Data.CommandType.StoredProcedure };
           comm.Parameters.AddWithValue("@TrackID", trackId);
           comm.Parameters.AddWithValue("@EventID", eventId);
           try
           {
               conn.Open();
               var reader = comm.ExecuteReader();
               while (reader.Read())
               {
                   if(sessionsList == null)
                   {
                      sessionsList = new List<Session>();
                   }
                   var session = new Session
                                     {
                                         ID = new Guid(reader["ID"].ToString()),
                                         Title = reader["Title"].ToString(),
                                         Description = reader["Description"].ToString(),
                                         EventID = new Guid(reader["EventID"].ToString()),
                                         Language = reader["Language"].ToString(),
                                         Speakers = GetAllSpeakersForSession(reader["ID"].ToString()),
                                         TimeSlot = new TimeSlots
                                                        {
                                                            From = Convert.ToDateTime(reader["StartFrom"]),
                                                            To = Convert.ToDateTime(reader["EndsAt"])
                                                        }
                                     };
                   sessionsList.Add(session);
               }
               reader.Close();
           }
           catch(Exception ex)
           {
                
               log.Error(ex);
               throw;
           }
           finally
           {
               conn.Close();
           }
           return sessionsList;
       }

       /// <summary>
       /// Returns all sessions in the database
       /// </summary>
       /// <returns></returns>
       public List<Session> GetAllSessions()
       {
           List<Session> sessionList = null ;
           var conn = connString.GetConnection();
           var comm = new SqlCommand("GetAllSessions", conn) {CommandType = System.Data.CommandType.StoredProcedure};
           try
           {
               conn.Open();
               var reader = comm.ExecuteReader();
               while (reader.Read())
               {
                   if(sessionList == null)
                   {
                       sessionList = new List<Session>();
                   }
                   var session = new Session
                                     {
                                         ID = new Guid(reader["ID"].ToString()),
                                         Title = reader["Title"].ToString(),
                                         Description = reader["Description"].ToString(),
                                         EventID = new Guid(reader["EventID"].ToString()),
                                         EventName = reader["EventName"].ToString(),
                                         Language = reader["Language"].ToString(),
                                         Speakers = GetAllSpeakersForSession(reader["ID"].ToString())
                                     };
                   
                   sessionList.Add(session);
               }
               reader.Close();

           }
           catch(Exception ex)
           {
                
               log.Error(ex);
               throw;
           }
           finally
           {
               conn.Close();
           }
           return sessionList;
       }

       /// <summary>
       /// Returns the session with id passed as parametar
       /// </summary>
       /// <param name="id"></param>
       /// <returns></returns>
       public Session GetSessionById(Guid id)
       {
           Session session = null;
           var conn = connString.GetConnection();
           var comm = new SqlCommand("GetSessionByID", conn) { CommandType = System.Data.CommandType.StoredProcedure };
           comm.Parameters.AddWithValue("@id", id);
           try
           {
               conn.Open();
               var reader = comm.ExecuteReader();
               if(reader.Read())
               {
                   session = new Session
                   {
                       ID = new Guid(reader["ID"].ToString()),
                       Title = reader["Title"].ToString(),
                       Description = reader["Description"].ToString(),
                       EventID = new Guid(reader["EventID"].ToString()),
                       Language = reader["Language"].ToString(),
                       Speakers = GetAllSpeakersForSession(reader["id"].ToString()),
                       FinishedSessionImages = reader["Images"].ToString()
                   };
               }
               reader.Close();
           }
           catch (Exception ex)
           {
                
               log.Error(ex);
               throw;
           }
           finally
           {
               conn.Close();
           }
           return session;
       }

       /// <summary>
       /// Returns all speakers for session with id passed as parametar
       /// </summary>
       /// <param name="id"></param>
       /// <returns></returns>
       public List<Speaker> GetAllSpeakersForSession(string id)
       {
           List<Speaker> speakersList = null;
           var conn = connString.GetConnection();
           var comm = new SqlCommand("GetSpeakersForSession", conn)
                          {CommandType = System.Data.CommandType.StoredProcedure};
           try
           {
               comm.Parameters.AddWithValue("@SessionID", new Guid(id));
               conn.Open();
               var reader = comm.ExecuteReader();
               while(reader.Read())
               {
                   if(speakersList == null)
                   {
                       speakersList = new List<Speaker>();
                   }
                   var speaker = new Speaker
                                     {
                                         ID = new Guid(reader["ID"].ToString()),
                                         FirstName = reader["FirstName"].ToString(),
                                         LastName = reader["LastName"].ToString()
                                     };
                   speakersList.Add(speaker);
               }
               reader.Close();
           }
           catch (Exception ex)
           {
                
               log.Error(ex);
               throw;
           }
           return speakersList;
       }

       /// <summary>
       /// Updates existing session in the database
       /// </summary>
       /// <param name="session"></param>
       public void UpdateSession(Session session)
       {
           var conn = connString.GetConnection();
           var comm = new SqlCommand("UpdateSession", conn) {CommandType = System.Data.CommandType.StoredProcedure};
           comm.Parameters.AddWithValue("@ID", session.ID);
           comm.Parameters.AddWithValue("@Title", session.Title);
           comm.Parameters.AddWithValue("@Description", session.Description);
           comm.Parameters.AddWithValue("@EventID", session.EventID);
           comm.Parameters.AddWithValue("@Language", session.Language);
           
           try
           {
               conn.Open();
               comm.ExecuteNonQuery();
               DeleteSpeakersForSession(session);
               InsertSpeakersForSession(session);
           }
           catch(Exception ex)
           {
                
               log.Error(ex);
               throw;
           }
           finally
           {
               conn.Close();
           }
       }
       
       /// <summary>
       /// Deletes session from the database
       /// </summary>
       /// <param name="session"></param>
       public void DeleteSession(Session session)
       {
           var conn = connString.GetConnection();
           var comm = new SqlCommand("DeleteSession", conn) {CommandType = System.Data.CommandType.StoredProcedure};
           comm.Parameters.AddWithValue("@ID", session.ID);
           try
           {
               conn.Open();
               comm.ExecuteNonQuery();
           }
           catch(Exception ex)
           {
                
               log.Error(ex);
               throw;
           }
           finally
           {
               conn.Close();
           }
       }

       /// <summary>
       /// Removes session from track
       /// </summary>
       /// <param name="sessionId"></param>
       public void DeleteSessionFromTrack(Guid sessionId)
       {
           var conn = connString.GetConnection();
           var comm = new SqlCommand("DeleteSessionFromTrack", conn) { CommandType = System.Data.CommandType.StoredProcedure };
           comm.Parameters.AddWithValue("@SessionID", sessionId);
           try
           {
               conn.Open();
               comm.ExecuteNonQuery();
           }
           catch(Exception ex)
           {
                
               log.Error(ex);
               throw;
           }
           finally
           {
               conn.Close();
           }
       }
       
       /// <summary>
       /// Inserts new session in the database
       /// </summary>
       /// <param name="session"></param>
       public string InsertSession(Session session)
        {
            var conn = connString.GetConnection();
            var comm = new SqlCommand("InsertSession", conn) {CommandType = System.Data.CommandType.StoredProcedure};
            comm.Parameters.AddWithValue("@Title", session.Title);
            comm.Parameters.AddWithValue("@Description", session.Description);
            comm.Parameters.AddWithValue("@EventID", session.EventID);
            comm.Parameters.AddWithValue("@Language", session.Language);
            try
            {
                conn.Open();
                var id = new Guid(comm.ExecuteScalar().ToString());
                session.ID = id;
                InsertSpeakersForSession(session);
            }
            catch(Exception ex)
            {
                 
                log.Error(ex);
                throw;
            }
            finally
            {
                conn.Close();
            }
           return session.ID.ToString();
        } 

       /// <summary>
       /// Inserts speakers for existing session 
       /// </summary>
       /// <param name="session"></param>
       public void InsertSpeakersForSession(Session session)
       {

           var conn = connString.GetConnection();
           foreach(var speaker in session.Speakers)
           {
               var comm = new SqlCommand("InsertSpeakerForSession", conn)
                              {CommandType = System.Data.CommandType.StoredProcedure};
               comm.Parameters.AddWithValue("@SpeakerID", speaker.ID);
               comm.Parameters.AddWithValue("@SessionID", session.ID);
               
               try
               {
                   conn.Open();
                   comm.ExecuteNonQuery();
               }
               catch (Exception ex)
               {
                    
                   log.Error(ex);
                   throw;
               }
               finally
               {
                   conn.Close();
               }
           }
       }

       /// <summary>
       /// Deletes speakers for existing session
       /// </summary>
       /// <param name="session"></param>
       public void DeleteSpeakersForSession(Session session)
       {
           var conn = connString.GetConnection();
           foreach (var speaker in session.Speakers)
           {
               var comm = new SqlCommand("DeleteSpeakersForSession", conn) { CommandType = System.Data.CommandType.StoredProcedure };
               comm.Parameters.AddWithValue("@id", session.ID);

               try
               {
                   conn.Open();
                   comm.ExecuteNonQuery();
               }
               catch (Exception ex)
               {
                    
                   log.Error(ex);
                   throw;
               }
               finally
               {
                   conn.Close();
               }
           }
       }

       /// <summary>
       /// Inserts link to skydrive image album
       /// </summary>
       /// <param name="url"></param>
       /// <param name="id"></param>
       public void InsertImagesForSession(string url, Guid id)
       {
           var conn = connString.GetConnection();

           var comm = new SqlCommand("SessionAlbums", conn) { CommandType = System.Data.CommandType.StoredProcedure };
           comm.Parameters.AddWithValue("@id", id);
           comm.Parameters.AddWithValue("@link", url);
           try
           {
               conn.Open();
               comm.ExecuteNonQuery();
           }
           catch (Exception ex)
           {
                
               log.Error(ex);
               throw;
           }
           finally
           {
               conn.Close();
           }


       }
        
       /// <summary>
       /// Returns all sessions for speaker with id passed in parametars
       /// </summary>
       /// <param name="id"></param>
       /// <returns></returns>
       public List<Session> GetSessionsForSpeaker(Guid id)
       {
           List<Session> sessionsList = null;
           var conn = connString.GetConnection();
           var comm = new SqlCommand("GetSessionsForSpeaker") { CommandType = System.Data.CommandType.StoredProcedure };
           comm.Parameters.AddWithValue("@SpeakerID", id);
           comm.Connection = conn;
           try
           {
               conn.Open();
               var reader = comm.ExecuteReader();
               while (reader.Read())
               {
                   if(sessionsList == null)
                   {
                       sessionsList = new List<Session>();
                   }
                   var session = new Session
                   {
                       ID = new Guid(reader["ID"].ToString()),
                       Title = reader["Title"].ToString(),
                       Description = reader["Description"].ToString(),
                       EventID = new Guid(reader["EventID"].ToString()),
                       Language = reader["Language"].ToString(),
                       Speakers = GetAllSpeakersForSession(reader["ID"].ToString())
                   };
                   sessionsList.Add(session);
               }
               reader.Close();
           }
           catch (Exception ex)
           {
               
               log.Error(ex);
               throw;
           }
           finally
           {
               conn.Close();
           }
           return sessionsList;
       }
   }
}
